const express = require('express');
const distributeRouter = express.Router();
const { sendResult } = require('../utils/index.js')

const mysql = require('mysql')
const db = mysql.createPool({
    host: "127.0.0.1",
    user: "root",
    password: "admin123",
    database: "mydatabase_01",
    port: 3306,
})


const table = {
    name: 'distribute',
}

distributeRouter.use(express.json());
distributeRouter.use(express.urlencoded({ extended: true }));

const povertyCompositionOptions = ['低保户', '一般贫困', '五保户']
const ageDistributionOptions = ['16岁以下', '16-30岁', '31-45岁', '46-60岁', '60岁以上']
const populationSizeOptions = [
    '一人户',
    '二人户',
    '三人户',
    '四人户',
    '五人户',
    '六人户及以上'
]

const setObj = (obj, options) => {
    if (!obj) return {}
    let res = []
    let index = 0
    for (let key in obj) {
        res.push({
            label: options[index],
            value: obj[key],
            [key]: obj[key]
        })
        index++
    }
    console.log(res, 'res');

    return JSON.stringify(res)
}

//查找
distributeRouter.get('/distribute/list', (req, res) => {
    const { pageSize, pages } = req.query
    const startIndex = (pages - 1) * pageSize
    console.log(pageSize, pages, startIndex, 'list');
    let sql = `SELECT * FROM ${table.name} limit ${pageSize} offset ${startIndex}`

    if (pages && pageSize) {

        db.query(sql, (err, result) => {
            if (err) {
                console.log(err); return

            } else {
                sendResult(res, result)
                return
            }
        })
    } else {
        sql = `SELECT * FROM ${table.name}`
        db.query(sql, (err, result) => {
            if (err) {
                console.log(err); return
            }
            else {
                sendResult(res, result)
                return
            }

        })
    }
})

//新增 修改
distributeRouter.post('/distribute/edit', (req, res) => {
    let { id } = req.query
    let { countyName, povertyComposition, ageDistribution, populationSize } = req.body
    povertyComposition = setObj(povertyComposition, povertyCompositionOptions)
    ageDistribution = setObj(ageDistribution, ageDistributionOptions)
    populationSize = setObj(populationSize, populationSizeOptions)
    if (id) {
        if (countyName && povertyComposition && ageDistribution && populationSize) {
            db.query(`UPDATE ${table.name} SET countyName = '${countyName}',povertyComposition = '${povertyComposition}',ageDistribution = '${ageDistribution}', populationSize = '${populationSize}' WHERE id = ${id}`, (err, result) => {
                if (err) {
                    res.status(500).send(err.response)
                } else {
                    sendResult(res, {
                        msg: '修改成功'
                    })
                }
            }
            )
        } else {
            sendResult(res, {
                msg: '请输入完整信息'
            }, 201)
        }
    } else {
        if (!countyName || !povertyComposition || !ageDistribution || !populationSize) {
            sendResult(res, {
                msg: '请输入完整信息'
            }, 201)
        } else {
            db.query(`INSERT INTO ${table.name} (countyName,  povertyComposition, ageDistribution, populationSize) VALUES ('${countyName}', '${povertyComposition}', '${ageDistribution}', '${populationSize}')`, (err, result) => {
                if (err) {
                    console.log(err.errno);
                    if (err.errno === 1062) {
                        sendResult(res, {
                            msg: '该数据已存在'
                        }, 201)
                    } else {
                        res.status(500).send(err)
                    }

                } else {
                    sendResult(res, {
                        msg: '新增成功！'
                    })
                }
            })
        }

    }

})

//删除
distributeRouter.get('/distribute/del', (req, res) => {
    let { id } = req.query
    db.query(`DELETE FROM ${table.name} WHERE id = ${id}`, (err, result) => {
        if (err) {
            res.status(500).send(err)
        } else {
            sendResult(res, {
                msg: '删除成功'
            })
        }
    })
})


let countiesName = 'allcounty'
//获取全国贫困县数据
distributeRouter.get('/distribute/all-county', (req, res) => {
    const { pageSize, pages } = req.query
    const startIndex = (pages - 1) * pageSize
    console.log(pageSize, pages, startIndex, 'list');
    let sql = `SELECT * FROM ${countiesName} limit ${pageSize} offset ${startIndex}`

    if (pages && pageSize) {

        db.query(sql, (err, result) => {
            if (err) {
                console.log(err); return

            } else {
                sendResult(res, result)
                return
            }
        })
    } else {
        sql = `SELECT * FROM ${countiesName}`
        db.query(sql, (err, result) => {
            if (err) {
                console.log(err); return
            }
            else {
                sendResult(res, result)
                return
            }
        })
    }
})

module.exports = distributeRouter;